package cn.easybuy.dao.impl;

import cn.easybuy.dao.BaseDao;
import cn.easybuy.dao.ProductDAO;
import cn.easybuy.entity.PageInfo;
import cn.easybuy.entity.Product;
import cn.easybuy.entity.ProductCategory;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductDAOImpl extends BaseDao implements ProductDAO {
    /**
     * 查询所有商品
     * @return
     * @throws SQLException
     */
    @Override
    public PageInfo<Product> proListAll(PageInfo<Product> page) throws SQLException {
        ArrayList<Product> list = new ArrayList<>();
        String sql = "select * from easybuy_product limit ?,?;";
        Object[] obj =  new Object[2];
        obj[0] = (page.getPageNo() - 1) * page.getPageSize();
        obj[1] = page.getPageSize();
        ResultSet rs = executeQuery(sql,obj);
        while (rs.next()){
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String description = rs.getString("description");
            float price = rs.getFloat("price");
            int stock = rs.getInt("stock");
            int categoryLevel1Id = rs.getInt("categoryLevel1Id");
            int categoryLevel2Id = rs.getInt("categoryLevel2Id");
            int categoryLevel3Id = rs.getInt("categoryLevel3Id");
            String fileName = rs.getString("fileName");
            int isDelete = rs.getInt("isDelete");
            Product product = new Product(id,name,description,price,stock,categoryLevel1Id,categoryLevel2Id,categoryLevel3Id,fileName,isDelete);
            list.add(product);
        }
        page.setPageList(list);
        String sqlCount = "select count(*) total from easybuy_product";
        ResultSet resultSet = executeQuery(sqlCount, new Object[0]);
        int count = 0;
        if (resultSet.next()) {
            count = resultSet.getInt("total");
        }
        int size = page.getPageSize();
        int num = count % size == 0 ? count / size : count / size + 1;
        page.setPageNum(num);
        page.setPageCount(count);

        return page;
    }

    @Override
    public PageInfo<ProductCategory> getCategoryList( PageInfo<ProductCategory> page) throws SQLException {
        ArrayList<ProductCategory> list =new ArrayList<>();
//        String sql = "select * from easybuy_product_category;";
        //select c1.*,c2.`name`,c2.id from easybuy_product_category c1 left join easybuy_product_category c2 on c1.`parentId`=c2.`id`
        String sql="select c1.*,c2.name parentName from easybuy_product_category c1 left join easybuy_product_category c2 on c1.parentId=c2.id limit ?,?;";
        Object[] obj =  new Object[2];
        obj[0] = (page.getPageNo() - 1) * page.getPageSize();
        obj[1] = page.getPageSize();
        ResultSet rs = executeQuery(sql,obj);
        while (rs.next()){
            int id = rs.getInt("id");
            String name = rs.getString("name");
            int parentId = rs.getInt("parentId");
            int type = rs.getInt("type");
            String iconClass = rs.getString("iconClass");
            String parentName = rs.getString("parentName");
            ProductCategory productCategory = new ProductCategory(id,name,parentId,type,iconClass);
            productCategory.setParentName(parentName);
            list.add(productCategory);
        }
        page.setPageList(list);
        String sqlCount = "select count(*) total from easybuy_product_category";
        ResultSet resultSet = executeQuery(sqlCount, new Object[0]);
        int count = 0;
        if (resultSet.next()) {
            count = resultSet.getInt("total");
        }
        int size = page.getPageSize();
        int num = count % size == 0 ? count / size : count / size + 1;
        page.setPageNum(num);
        page.setPageCount(count);

        return page;
    }



    @Override
    public int categoryAdd(int cate1,int cate2, String name) throws SQLException {
        String sql;
        Object[] obj = new Object[3];
        if (cate1==0){
            sql= "insert into easybuy_product_category values(null,?,?,?,null)";

            obj[0] = name;
            obj[1] = 0;
            obj[2] = 1;
        }else if (cate2==0){
             sql = "insert into easybuy_product_category values(null,?,?,?,null)";
            obj[0] = name;
            obj[1] = cate1;
            obj[2] = 2;
        }else {
            sql = "insert into easybuy_product_category values(null,?,?,?,null)";
            obj[0] = name;
            obj[1] = cate2;
            obj[2] = 3;
        }
        return executeUpdate(sql, obj);
    }

    @Override
    public List<ProductCategory> getCate1(int pid,int type) throws SQLException {
        List<ProductCategory> list = new ArrayList<>();
        StringBuilder sql =new StringBuilder("select * from easybuy_product_category where type =?");
        Object[] obj = null;
        if(pid!=0){
            sql.append(" and parentId=?");
            obj =new Object[2];
            obj[0]=type;
            obj[1]=pid;
        }else{
            obj =new Object[1];
            obj[0]=type;
        }
        ResultSet rs = executeQuery(sql.toString(), obj);
        while (rs.next()){
            int id = rs.getInt("id");
            String name = rs.getString("name");
            int parentId = rs.getInt("parentId");
            int type1 = rs.getInt("type");
            String iconClass = rs.getString("iconClass");
            ProductCategory category = new ProductCategory(id, name, parentId, type1, iconClass);
            list.add(category);
        }
        return list;
    }

    @Override
    public int categoryDel(int id) throws SQLException {
        String sql = "delete from easybuy_product_category where id =?";
        Object[] obj = new Object[1];
        obj[0] = id;
        return executeUpdate(sql,obj);
    }

    @Override
    public int productAdd(Product product) throws SQLException {
        String sql = "insert into easybuy_product values(null,?,?,?,?,?,?,?,?,null)";
        Object[] obj = new Object[8];
        obj[0] = product.getName();
        obj[1] = product.getDescription();
        obj[2] = product.getPrice();
        obj[3] = product.getStock();
        obj[4] = product.getCategoryLevel1Id();
        obj[5] = product.getCategoryLevel2Id();
        obj[6] = product.getCategoryLevel3Id();
        obj[7] = product.getFileName();
        return executeUpdate(sql,obj);

    }

    @Override
    public Product getProductById(int id) throws SQLException {
        String sql = "select * from easybuy_product where id = ?";
        Object[] obj = new Object[1];
        obj[0] = id;
        ResultSet rs = executeQuery(sql, obj);
        if (rs.next()){
            String name = rs.getString("name");
            String description = rs.getString("description");
            float price = rs.getFloat("price");
            int stock = rs.getInt("stock");
            int categoryLevel1Id = rs.getInt("categoryLevel1Id");
            int categoryLevel2Id = rs.getInt("categoryLevel2Id");
            int categoryLevel3Id = rs.getInt("categoryLevel3Id");
            String fileName = rs.getString("fileName");
            int isDelete = rs.getInt("isDelete");
            Product product = new Product(id,name,description,price,stock,categoryLevel1Id,categoryLevel2Id,categoryLevel3Id,fileName,isDelete);
            return product;
        }
       return null;
    }

    @Override
    public int productUpd(Product product) throws SQLException {
        String sql = "update easybuy_product set name=?,description=?,price=?,stock=?,categoryLevel1Id=?,categoryLevel2Id=?,categoryLevel3Id=?,fileName=? where id=?";
        Object[] obj = new Object[9];
        obj[0] = product.getName();
        obj[1] = product.getDescription();
        obj[2] = product.getPrice();
        obj[3] = product.getStock();
        obj[4] = product.getCategoryLevel1Id();
        obj[5] = product.getCategoryLevel2Id();
        obj[6] = product.getCategoryLevel3Id();
        obj[7] = product.getFileName();
        obj[8] = product.getId();
        return executeUpdate(sql,obj);
    }

    @Override
    public int productDel(int id) throws SQLException {
        String sql = "delete from easybuy_product where id =?";
        Object[] obj = new Object[1];
        obj[0] = id;
        return executeUpdate(sql,obj);
    }

    public List<ProductCategory> getALlCate(ProductCategory category) throws SQLException {
        List<ProductCategory> list = new ArrayList<>();
        String sql="select * from easybuy_product_category where parentId=? and type=?";

        Object[] obj = new Object[2];
        obj[0]=category.getParentId();
        obj[1]=category.getType();

        ResultSet rs = executeQuery(sql, obj);
        while (rs.next()){
            //把所有的字段获取出来 封装到ProductCategory对象中
            int id = rs.getInt("id");
            String name = rs.getString("name");
            int parentId = rs.getInt("parentId");
            int type = rs.getInt("type");
            String iconClass = rs.getString("iconClass");
            ProductCategory p = new ProductCategory(id,name,parentId,type,iconClass);
            list.add(p);
        }
        return list;
    }

    @Override
    public List<Product> getProDuctList(int cateId) throws SQLException {
        List<Product> list = new ArrayList<>();
        String sql = "select * from  easybuy_product where categoryLevel1Id=? and isDelete=0";

        Object[] obj = new Object[1];
        obj[0]=cateId;
        ResultSet rs = executeQuery(sql, obj);
        while (rs.next()){
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String description = rs.getString("description");
            float price = rs.getFloat("price");
            int stock = rs.getInt("stock");
            int categoryLevel1Id = rs.getInt("categoryLevel1Id");
            int categoryLevel2Id = rs.getInt("categoryLevel2Id");
            int categoryLevel3Id = rs.getInt("categoryLevel3Id");
            String fileName = rs.getString("fileName");
            Product product = new Product(id, name, description, price, stock, categoryLevel1Id, categoryLevel2Id, categoryLevel3Id, fileName, 0);
            list.add(product);
        }
        return list;
    }

    @Override
    public Product getProById(int id) throws SQLException {
        String sql ="select * from easybuy_product where id=?";
        Object[] obj = new Object[1];
        obj[0]=id;
        ResultSet rs = executeQuery(sql, obj);
        Product product=new Product();
        if(rs.next()){
            String name = rs.getString("name");
            String description = rs.getString("description");
            float price = rs.getFloat("price");
            int stock = rs.getInt("stock");
            int categoryLevel1Id = rs.getInt("categoryLevel1Id");
            int categoryLevel2Id = rs.getInt("categoryLevel2Id");
            int categoryLevel3Id = rs.getInt("categoryLevel3Id");
            String fileName = rs.getString("fileName");
            product = new Product(id, name, description, price, stock, categoryLevel1Id, categoryLevel2Id, categoryLevel3Id, fileName, 0);
        }
        return product;
    }

    @Override
    public List<Product> getProListCate2(int id,int type) throws SQLException {
        List<Product> list = new ArrayList<>();

        String sql = "";
        if(type==2){
            sql="select * from easybuy_product where categoryLevel2Id=?";
        }else if(type==3){
            sql="select * from easybuy_product where categoryLevel3Id=?";
        }else if (type==1){
            sql="select * from easybuy_product where categoryLevel1Id=?";
        }
        Object[] obj = new Object[1];
        obj[0] = id;
        ResultSet rs = executeQuery(sql, obj);
        while (rs.next()){
            int zid = rs.getInt("id");
            String name = rs.getString("name");
            String description = rs.getString("description");
            float price = rs.getFloat("price");
            int stock = rs.getInt("stock");
            int categoryLevel1Id = rs.getInt("categoryLevel1Id");
            int categoryLevel2Id = rs.getInt("categoryLevel2Id");
            int categoryLevel3Id = rs.getInt("categoryLevel3Id");
            String fileName = rs.getString("fileName");
            Product product = new Product(zid, name, description, price, stock, categoryLevel1Id, categoryLevel2Id, categoryLevel3Id, fileName, 0);
            list.add(product);
        }
        return list;
    }


}
